
package com.scalar.chiptrack.marketing.dao;


import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Iterator;

import org.apache.struts.util.LabelValueBean;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.marketing.MarketingForecast;
import com.scalar.chiptrack.operations.process.FinishedGoods;
import com.scalar.chiptrack.operations.process.dao.FinishedGoodsDAO;
import com.scalar.chiptrack.sales.CustomerRelationship;
import com.scalar.chiptrack.sales.dao.CustomerRelationshipDAO;
import com.scalar.chiptrack.utils.SqlQueryMappings;


public class MarketingDAO
{

   // ----------------------------------------------------- Instance Variables


   /**
    * Default Constructor
    *
    */
   public MarketingDAO()
   {

   }


   // --------------------------------------------------------- Public Methods

   /**
    * Get the ProductId for the marketingPartNo
    *
    * @param marketingPartNo
    * @return ProductID
    */
   public static int loadProductId(String marketingPartNo) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int productId = -1;
        if(marketingPartNo == null)
            return productId;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("marketing.pricing.getProductId"));
            pstmt.setString (1, marketingPartNo);

            rs = pstmt.executeQuery();

            if (rs.next() == false) {
                //Not able to find the productId
            }
            else {
                productId = rs.getInt("Product_ID");
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Product ID from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        //Commented by Krishna Prasad on 8 May 2004, as it is printing the Tomcat Console Window
        //System.out.println("Marketing DAO " + productId);
        return productId;

   }
  //added by varma 15 jul 2004 to get the product id based on manufacturing part number
  public static int loadProductIdByMfgPartNo(String mfgPartNo) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int productId = -1;
        if(mfgPartNo == null)
            return productId;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.getProductIdWithManufacturingPartNoForSearch"));
            pstmt.setString (1, mfgPartNo);

            rs = pstmt.executeQuery();

            if (rs.next() == false) {
                //Not able to find the productId
            }
            else {
                productId = rs.getInt("Product_ID");
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Product ID from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        //Commented by Krishna Prasad on 8 May 2004, as it is printing the Tomcat Console Window
        //System.out.println("Marketing DAO " + productId);
        return productId;

   }


   // Added by Basha (08/11/2003)
   public static int loadProductIdForSearch(String manufacturingPartNo) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int productId = -1;
        if(manufacturingPartNo == null)
            return productId;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.salesOrder.getProductIdWithManufacturingPartNoForSearch"));
            pstmt.setString (1, manufacturingPartNo);

            rs = pstmt.executeQuery();

            if (rs.next() == false) {
                //Not able to find the productId
            }
            else {
                productId = rs.getInt("Product_ID");
            }
        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Product ID from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }
        //System.out.println("Marketing DAO " + productId);
        return productId;

   }
   // Added by Basha (08/11/2003)


  /**
    * Get all the Forecast details for the marketingPartNo and Customer
    *
    * @param productId
    * @param customerName
    * @return ArrayList forecastDetails
    */
   public static ArrayList loadForecastDetails(String productId, String customerName) throws ScalarException
   {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();

        if (((productId == null) || (productId == "-1")) && ((customerName == null) || (customerName == "0")))
        {
            return list;
        }

        try {
            conn = DataSource.getConnection();

            Calendar nowCal = new GregorianCalendar();
            nowCal.setTime(new java.util.Date());

            //System.out.println("Calendar = " + nowCal.get(Calendar.MONTH) + " , " + nowCal.get(Calendar.YEAR));
            //pstmt = conn.prepareStatement (SqlQueryMappings.get ("marketing.forecast.loadForecastDetails"));
            pstmt = conn.prepareStatement ("SELECT * FROM Marketing_Forecast_Table WHERE Product_ID = ? and Customer_Name = ? and Forecast_Month >= ? and Forecast_Year = ?");
            pstmt.setInt (1, Integer.parseInt(productId));
            pstmt.setString (2, customerName);
            pstmt.setInt (3, nowCal.get(Calendar.MONTH) + 1);
            pstmt.setInt (4, nowCal.get(Calendar.YEAR));

            rs = pstmt.executeQuery();

            MarketingForecast forecast = null;
            while (rs.next()) {
                /*forecast = new MarketingForecast(
                                rs.getInt("Product_ID"),
                                rs.getString("Customer_Name"),
                                rs.getInt("Forecast_Month"),
                                rs.getInt("Forecast_Year"),
                                rs.getInt("Forecast_Qty"),
                                rs.getFloat("Unit_Price"),
                                rs.getFloat("Revenue_Amount"));*/

                forecast.setMonthName(getMonthName(forecast.getMonth()-1));
                list.add(forecast);
            }

            pstmt.close();
            pstmt = null;
            rs.close();
            rs = null;

            //This needs to be changed..because of the work week idea i did like this. if we need to change this needs be changed
            pstmt = conn.prepareStatement ("SELECT * FROM Marketing_Forecast_Table WHERE Product_ID = ? and Customer_Name = ? and Forecast_Year > ?");
            pstmt.setInt (1, Integer.parseInt(productId));
            pstmt.setString (2, customerName);
            pstmt.setInt (3, nowCal.get(Calendar.YEAR));

            rs = pstmt.executeQuery();
            while (rs.next()) {
                /*forecast = new MarketingForecast(
                                rs.getInt("Product_ID"),
                                rs.getString("Customer_Name"),
                                rs.getInt("Forecast_Month"),
                                rs.getInt("Forecast_Year"),
                                rs.getInt("Forecast_Qty"),
                                rs.getFloat("Unit_Price"),
                                rs.getFloat("Revenue_Amount"));*/

                forecast.setMonthName(getMonthName(forecast.getMonth()-1));
                list.add(forecast);
            }

        }
        catch (Exception sqlex) {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not load forecast details from database", sqlex);
        }
        finally {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return list;

   }

    public static void saveForecastDetails(DBTransaction dbTransaction,String productId, String customerName, String month, String year,
            String qty, String unitPrice) throws ScalarException
   {

        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rs = null;
        boolean success = false;

        try {
            // Get the connection from regular datasource if its not in a manual transaction
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            //pstmt = conn.prepareStatement (SqlQueryMappings.get ("sales.auditTrail.insertInfo"));
            cstmt = conn.prepareCall ("{Call sp_update_forecast_details(?,?,?,?,?,?)}");
            cstmt.setInt (1, Integer.parseInt(productId));
            cstmt.setString (2, customerName);
            cstmt.setInt (3, Integer.parseInt(month));
            cstmt.setInt (4, Integer.parseInt(year));
            cstmt.setInt (5, Integer.parseInt(qty));
            cstmt.setFloat (6, Float.parseFloat(unitPrice));

            cstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not insert forecast details into database", sqle);
        }
        finally {
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true)
                            conn.commit();
                        else
                            conn.rollback();
                    }
                }
                catch (SQLException ignore) {}
            }
            if (cstmt != null) {
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

    }

    public static String getMonthName(int month) throws Exception
    {

        switch(month) {
            case 0:
                return "Jan";
            case 1:
                return "Feb";
            case 2:
                return "Mar";
            case 3:
                return "Apr";
            case 4:
                return "May";
            case 5:
                return "Jun";
            case 6:
                return "Jul";
            case 7:
                return "Aug";
            case 8:
                return "Sep";
            case 9:
                return "Oct";
            case 10:
                return "Nov";
            case 11:
                return "Dec";
            default:
                return null;
        }
    }

    // Added by Prabhanjan on 18 Jun 2004 to get Month Number by Month Name
    public static int getMonth (String monthName) throws Exception
    {
        if ( monthName.equalsIgnoreCase ( "Jan" ) )
            return 0;
        else if ( monthName.equalsIgnoreCase ( "Feb" ) )
            return 1;
        else if ( monthName.equalsIgnoreCase ( "Mar" ) )
            return 2;
        else if ( monthName.equalsIgnoreCase ( "Apr" ) )
            return 3;
        else if ( monthName.equalsIgnoreCase ( "May" ) )
            return 4;
        else if ( monthName.equalsIgnoreCase ( "Jun" ) )
            return 5;
        else if ( monthName.equalsIgnoreCase ( "Jul" ) )
            return 6;
        else if ( monthName.equalsIgnoreCase ( "Aug" ) )
            return 7;
        else if ( monthName.equalsIgnoreCase ( "Sep" ) )
            return 8;
        else if ( monthName.equalsIgnoreCase ( "Oct" ) )
            return 9;
        else if ( monthName.equalsIgnoreCase ( "Nov" ) )
            return 10;
        else if ( monthName.equalsIgnoreCase ( "Dec" ) )
            return 11;
        else
            return 0;
    }


    public static ArrayList getAllCustomerNames() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        ArrayList customers = new ArrayList();

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("select C_Name from Customer where C_Disabled = 1 ORDER BY C_Name");

            rs = pstmt.executeQuery();
            while(rs.next())
            {
                String name = rs.getString("C_Name");
                customers.add(new org.apache.struts.util.LabelValueBean(name,name));
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get Customers from database", sqlex);
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return customers;
   }

   // Added by Gandhi on 23 July 2004
    public static ArrayList loadAllManufacturingPartNumbersWithMarketingPartNumbers() throws ScalarException
   {

       Iterator finishedGoodsList = FinishedGoodsDAO.loadAllFinishedGoods().iterator();
       ArrayList list = new ArrayList();

       while(finishedGoodsList.hasNext()) {

            FinishedGoods finishedGoods = (FinishedGoods) finishedGoodsList.next();
            String mfgPartNo = finishedGoods.getManufacturingPartNo();
            String mktgPartNo = finishedGoods.getMarketingPartNo();
            if((mfgPartNo != null) && (mfgPartNo.length() != 0)) {
                list.add(new org.apache.struts.util.LabelValueBean(
                                 mktgPartNo,mfgPartNo));
            }
        }

       return list;
   }
   // End - Gandhi
   //Added by varma 11 sep 2004 top load End Users of Customer based on Customer Name
    public static ArrayList getEndUsersForCustomer(String customer) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int customerId = -1;
        ArrayList endUsers = new ArrayList();
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement("select customer_id from customer where c_name = ?");
            pstmt.setString(1,customer);
            rs = pstmt.executeQuery() ;
            if(rs.next())
            {
                customerId = rs.getInt("Customer_Id");
            }
            /*rs.close() ;
            pstmt.close() ;
            pstmt = conn.prepareStatement("SELECT E_Name From End_User_Table Where Customer_Id = ? ");
            pstmt.setInt ( 1, customerId  );
            rs = pstmt.executeQuery();
            while(rs.next())
            {
                String name = rs.getString("E_Name");
                endUsers.add(new org.apache.struts.util.LabelValueBean(name,name));
            } */
            Iterator odmList = CustomerRelationshipDAO.getAllOdmList( customerId+"" ).iterator();
            try
            {
               while ( odmList.hasNext() ) {
                    CustomerRelationship customerRelationship = (CustomerRelationship) odmList.next();
                    if ( "checked".equalsIgnoreCase( customerRelationship.getFlag() ) ) {
                        endUsers.add( new LabelValueBean( customerRelationship.getChildCustomerName(), customerRelationship.getChildCustomerName() ));
                    }
                }
            }
            catch( Exception e )
            {
                e.printStackTrace();
            }
        }
        catch (Exception sqlex)
        {
            sqlex.printStackTrace();
            throw new ScalarException ("Could not get End Users from database", sqlex);
        }
        finally
        {
            if (rs != null) {
                try { rs.close(); } catch (SQLException excp) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException excp) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException excp) {}
            }
        }

        return endUsers;
   }

     // Added by Gandhi on 8th Sep 2004
    public static ArrayList getAllSalesPersonsForCustomer() throws ScalarException
    {
       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;

       ArrayList salesList = new ArrayList();


       try {

             conn = DataSource.getConnection();
             //Modified by varma 23 Dec 2004
             //pstmt = conn.prepareStatement("select DISTINCT Sales_Person_Name from Sales_Persons_Table where Deactivated =0 or Deactivated is NULL");
             pstmt = conn.prepareStatement("select DISTINCT SalesPerson_Name from Sales_Persons where Deactivated <> 1");

             rs = pstmt.executeQuery();

             while(rs.next())
             {
                  String sales_person = rs.getString("SalesPerson_Name");
                 salesList.add ( new org.apache.struts.util.LabelValueBean ( sales_person, sales_person ) );
             }

           }
           catch (Exception sqlex) {
               sqlex.printStackTrace();
               throw new ScalarException ("Could not get Sales Persons from database", sqlex);
           }
           finally {
               if (rs != null) {
                   try { rs.close(); } catch (SQLException excp) {}
               }
               if (pstmt != null) {
                   try { pstmt.close(); } catch (SQLException excp) {}
               }
               if (conn != null) {
                   try { conn.close(); } catch (SQLException excp) {}
               }
           }

           return salesList;
      }


    public static ArrayList loadApprovedMarketingPartNumbers() throws ScalarException
   {

       Iterator iterator = FinishedGoodsDAO.loadAllFinishedGoods().iterator();
       ArrayList list = new ArrayList();

       while( iterator.hasNext() ) 
       {
            FinishedGoods fg = (FinishedGoods) iterator.next();

            list.add(new LabelValueBean( fg.getMarketingPartNo(), fg.getProductId() + "") );
        }

       return list;
   }
      // Ends here - Gandhi

    public static void main(String args[]) throws Exception
    {
        loadForecastDetails("100","66");
    }

}